import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt

# 加载数据
attachment1_data = pd.read_excel('../附件1.xlsx')
attachment2_data = pd.read_excel('../附件2.xlsx')

# 将作物名称替换为编号
crop_to_number = attachment2_data[['作物编号', '作物名称']].drop_duplicates().set_index('作物名称')['作物编号']
attachment2_data['作物编号'] = attachment2_data['作物名称'].map(crop_to_number)

# 创建数据透视表，显示每种作物在各地块的种植面积，这次使用作物编号作为列
crop_plot_matrix = pd.pivot_table(attachment2_data, values='种植面积/亩', index='种植地块', columns='作物编号', fill_value=0)

# 计算作物之间的相关性矩阵
correlation_matrix = crop_plot_matrix.corr()

# 绘制热力图，使用作物编号作为坐标
plt.figure(figsize=(15, 12), dpi=200)
sns.set(style="white")
heatmap = sns.heatmap(correlation_matrix, annot=False, cmap='coolwarm', linewidths=.5)
plt.title('Crop Correlation Heatmap Based on Plot Planting Patterns', fontsize=20)
plt.xlabel('Crop Number', fontsize=15)
plt.ylabel('Crop Number', fontsize=15)
plt.xticks(rotation=90)
plt.yticks(rotation=0)
plt.tight_layout()
plt.show()
